<?php

namespace app\office\controller;

use think\Controller;
use think\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Excel extends Controller
{
    private $sheet;

    private $mergeCellArray;

    public function index()
    {
        //
    }

    public function readTreeExcel()
    {
        $startColumn = 'A';
        $endColumn = 'C';
        $startRow = 92;
        $endRow = 93;
        $officeId = '1538302785760665601';
        $parentId = '7030303ff2c911ecb637d8bbc1155329';
        $parentIds = '0,3,';
        $filePath = 'D:/Users/Windows/WeChat Files/wxid_j8vt31y2h1xl22/FileStorage/MsgAttach/5e1f2cdd9decadb19d673731f5548525/File//2022-06/大数据中心文件目录表.xls';
        $excel = IOFactory::load($filePath);
        $this->sheet = $excel->getSheet(0);
        $mergeCellArray = $this->sheet->getMergeCells();
        foreach ($mergeCellArray as $eachMergeCell) {
            $cellArray = explode(':', $eachMergeCell);
            $this->mergeCellArray[$cellArray[0]] = [
                'startRow' => substr($cellArray[0], 1),
                'endRow' => substr($cellArray[1], 1),
                'startColumn' => ord(substr($cellArray[0], 0, 1)),
                'endColumn' => ord(substr($cellArray[1], 0, 1))
            ];
        }
        $valueArray = [];
        // dump($this->mergeCellArray);
        $this->readValue($startRow, $endRow, ord($startColumn), ord($endColumn), $officeId, $parentId, $parentIds, $valueArray);
        echo(join('</br>', $valueArray));
    }

    public function readValue($startRow, $endRow, $startColumn, $endColumn, $officeId, $parentId, $parentIds, &$valueArray)
    {
        $parentIds = $parentIds . $parentId . ',';
        $sort = 0;
        for ($i = $startRow; $i <= $endRow; $i++) {
            for ($o = $startColumn; $o <= $endColumn; $o++) {
                $sort += 1;
                $microtimeArray = explode(' ', microtime());
                $id = $microtimeArray[1] . str_replace('0.', '', $microtimeArray[0]);
                $cellNum = chr($o) . $i;
                $cellValue = $this->sheet->getCell($cellNum)->getValue();
                if ($cellValue && !empty($cellValue)) {
                    $valueArray[] = sprintf("INSERT INTO `document_management`.`tb_file_type` (`id`, `name`, `sort`, `parent_id`, `parent_ids`, `create_by`, `create_date`, `update_by`, `update_date`, `del_flag`, `office_id`, `grade`, `leaf_type`) VALUES ('%s', '%s', %d, '%s', '%s', '1', NOW(), '1', NOW(), '0', '%s', 6, 1);", $id, $cellValue, $sort * 30, $parentId, $parentIds, $officeId);
                    if (array_key_exists($cellNum, $this->mergeCellArray)) {
                        $mergeCell = $this->mergeCellArray[$cellNum];
                        $this->readValue($mergeCell['startRow'], $mergeCell['endRow'], $mergeCell['startColumn'] + 1, $mergeCell['endColumn'] + 1, $officeId, $id, $parentIds, $valueArray);
                        $i = $mergeCell['endRow'];
                    }
                }
            }
        }
    }
}
